package com.bookmall.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.bookmall.dao.UserDao;
import com.bookmall.entity.User;
import com.bookmall.util.DBUtil;

public class UserDaoImpl implements UserDao {

	@Override
	public boolean insert(User t) throws SQLException {
		// 1.得到连接
		Connection c = DBUtil.getConnection();
		// 2.创建Sql文
		String sql = "insert into user_tb(username,password,salt,nickname,address,phone) values(?,?,?,?,?,?)";
		// 3.得到句柄
		PreparedStatement ps = c.prepareStatement(sql);
		// 4.设置参数
		ps.setString(1, t.getUserName());
		ps.setString(2, t.getPassword());
		ps.setInt(3, t.getSalt());
		ps.setString(4, t.getNickName());
		ps.setString(5, t.getAddress());
		ps.setString(6, t.getPhone());
		// 5.执行
		int row = ps.executeUpdate();
		// 6.关闭
		DBUtil.closeConnection(c, ps, null);
		return row == 1 ? true : false;
	}

	@Override
	public boolean update(User t) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "update user_tb set userName=?,address = ? where userId=? and delete_flag=0";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, t.getUserName());
		ps.setString(2, t.getAddress());
		ps.setInt(3, t.getUserId());
		int row = ps.executeUpdate();
		DBUtil.closeConnection(c, ps, null);
		return row == 1 ? true : false;
	}

	@Override
	public boolean delete(int userId) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "update user_tb set delete_flag = 1 where userId = ? and delete_flag = 0";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, userId);
		int row = ps.executeUpdate();
		ps.close();
		return row == 1 ? true : false;
	}

	@Override
	public User selectById(int id) throws SQLException {
		Connection c = DBUtil.getConnection();
		String sql = "select userId,userName,password,salt,nickName,address,phone,create_Time,modify_Time from user_tb where userId=? and delete_Flag = 0";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			User user = new User();
			user.setUserId(rs.getInt(1));
			user.setUserName(rs.getString(2));
			user.setPassword(rs.getString(3));
			user.setSalt(rs.getInt(4));
			user.setNickName(rs.getString(5));
			user.setAddress(rs.getString(6));
			user.setPhone(rs.getString(7));
			user.setCreateTime(rs.getTimestamp(8).toLocalDateTime());
			if (rs.getTimestamp(9) != null) {
				user.setModifyTime(rs.getTimestamp(9).toLocalDateTime());
			}
			user.setDeleteFlag(rs.getBoolean(10));
			DBUtil.closeConnection(c, ps, rs);
			return user;
		} else {
			DBUtil.closeConnection(c, ps, rs);
			return null;
		}
	}

	@Override
	public List<User> selectByPage(int page, int pageSize) throws SQLException {
		Connection c = DBUtil.getConnection();
		List<User> users = new ArrayList<>();
		String sql = "select userId,userName,password,salt,nickName,address,phone,create_Time,modify_Time from user_tb where delete_Flag = 0 limit ?,?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, (page - 1) * pageSize);
		ps.setInt(2, pageSize);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			User user = new User();
			user.setUserId(rs.getInt(1));
			user.setUserName(rs.getString(2));
			user.setPassword(rs.getString(3));
			user.setSalt(rs.getInt(4));
			user.setNickName(rs.getString(5));
			user.setAddress(rs.getString(6));
			user.setPhone(rs.getString(7));
			user.setCreateTime(rs.getTimestamp(8).toLocalDateTime());
			if (rs.getTimestamp(9) != null) {
				user.setModifyTime(rs.getTimestamp(9).toLocalDateTime());
			}
			user.setDeleteFlag(rs.getBoolean(10));
			users.add(user);
		}
		DBUtil.closeConnection(c, ps, rs);
		return users.isEmpty() ? Collections.emptyList() : users;
	}

}
